package com.hctforgreen.greenservice.db;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.hctforgreen.greenservice.model.PwdEntity;

/**
 * 数据库操作类
 * 
 * @author ld 20131122
 * 
 */
public class DBManager {
	private DBHelper helper;
	private SQLiteDatabase db;
	private static final String TABLE_PWD = "table_pwd";

	public DBManager(Context context) {
		helper = new DBHelper(context);
		// 因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName, 0,
		// mFactory);
		// 所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里
		db = helper.getWritableDatabase();
	}

	/**
	 * add persons
	 * 
	 * @param persons
	 */
	public void add(List<PwdEntity> pwds) {
		db.beginTransaction(); // 开始事务
		try {
			for (PwdEntity pwd : pwds) {
				db.execSQL(
						"INSERT INTO "
								+ TABLE_PWD
								+ " VALUES(null, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?)",
						new Object[] { pwd.stripeCode, pwd.openPassword,
								pwd.projectName, pwd.ownerName, pwd.ownerPhone,
								pwd.note, pwd.date, pwd.time, pwd.province,
								pwd.city, pwd.district, pwd.street,
								pwd.industry, pwd.sparebootPwd,
								pwd.identityCode, pwd.verifyStatus,
								pwd.refuseReason });
			}
			db.setTransactionSuccessful(); // 设置事务成功完成
		} finally {
			db.endTransaction(); // 结束事务
		}
	}

	/**
	 * add persons
	 * 
	 * @param persons
	 */
	public void add(PwdEntity pwd) {
		db.beginTransaction(); // 开始事务
		try {
			db.execSQL(
					"INSERT INTO "
							+ TABLE_PWD
							+ " VALUES(null, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?)",
					new Object[] { pwd.stripeCode, pwd.openPassword,
							pwd.projectName, pwd.ownerName, pwd.ownerPhone,
							pwd.note, pwd.date, pwd.time, pwd.province,
							pwd.city, pwd.district, pwd.street, pwd.industry,
							pwd.sparebootPwd, pwd.identityCode,
							pwd.verifyStatus, pwd.refuseReason });
			db.setTransactionSuccessful(); // 设置事务成功完成
		} finally {
			db.endTransaction(); // 结束事务
		}
	}

	/**
	 * update warn's content
	 * 
	 * @param person
	 */
	public void updateAge(PwdEntity pwd) {
		ContentValues cv = new ContentValues();
		// cv.put("content", pwd.Content);
		// cv.put("day", pwd.day);
		// db.update(TABLE_WARN, cv, "_id = ?", new
		// String[]{String.valueOf(warn._id)});
	}

	/**
	 * 更新备用密码 
	 * @param Pwd
	 * @return
	 */
	public void updateSparebootPwd(PwdEntity Pwd) {
		ContentValues cv = new ContentValues();
		cv.put("sparebootPwd", Pwd.sparebootPwd);

		if (Pwd.identityCode != null) {

			db.update(
					TABLE_PWD,
					cv,
					"stripeCode = ?" + " and identityCode = ?",
					new String[] { String.valueOf(Pwd.stripeCode),
							String.valueOf(Pwd.identityCode) });
		} else {

			db.update(TABLE_PWD, cv, "stripeCode = ?",
					new String[] { String.valueOf(Pwd.stripeCode) });
		}

	}

	/**
	 * 更新报完成状态
	 * 
	 * @param Pwd
	 * @return
	 */
	public void updateCompleteStatus(PwdEntity Pwd) {
		ContentValues cv = new ContentValues();
		cv.put("verifyStatus", Pwd.verifyStatus);
		db.update(TABLE_PWD, cv, "identityCode =?",
				new String[] { String.valueOf(Pwd.identityCode) });

	}

	/**
	 * 更新大机组开机密码
	 * 
	 * @param Pwd
	 * @return
	 */
	public int updateBigUnitPwd(PwdEntity pwd) {
		int num = 0;
		ContentValues cv = new ContentValues();
		if (pwd.identityCode != null && pwd.openPassword != null) {
			if (pwd.openPassword.equals("拒绝申请")) {
				cv.put("openPassword", pwd.openPassword);
				cv.put("refuseReason", pwd.refuseReason);
				cv.put("verifyStatus", pwd.verifyStatus);
//				cv.put("updateTime", pwd.dateTime);
				num = db.update(TABLE_PWD, cv, "identityCode =?",
						new String[] { String.valueOf(pwd.identityCode) });
			} else {
				cv.put("openPassword", pwd.openPassword);
//				cv.put("updateTime", pwd.dateTime);
				cv.put("verifyStatus", pwd.verifyStatus);
				num = db.update(TABLE_PWD, cv, "identityCode =?",
						new String[] { String.valueOf(pwd.identityCode) });
			}
		}

		return num;
	}

	/**
	 * delete old warn
	 * 
	 * @param person
	 */
	public void deleteOldPwd(PwdEntity pwd) {
		db.delete(TABLE_PWD, "_id = ?",
				new String[] { String.valueOf(pwd._id) });
	}

	/**
	 * query all warns, return list
	 * 
	 * @return List<Person>
	 */
	public List<PwdEntity> queryAll() {
		ArrayList<PwdEntity> pwds = new ArrayList<PwdEntity>();
		Cursor c = queryTheCursor();
		while (c.moveToNext()) {
			PwdEntity pwd = new PwdEntity();
			pwd._id = c.getInt(c.getColumnIndex("_id"));
			pwd.identityCode = c.getString(c.getColumnIndex("identityCode"));
			pwd.verifyStatus = c.getString(c.getColumnIndex("verifyStatus"));
			pwd.refuseReason = c.getString(c.getColumnIndex("refuseReason"));
			pwd.stripeCode = c.getString(c.getColumnIndex("stripeCode"));
			pwd.openPassword = c.getString(c.getColumnIndex("openPassword"));
			pwd.projectName = c.getString(c.getColumnIndex("projectName"));
			pwd.ownerName = c.getString(c.getColumnIndex("ownerName"));
			pwd.ownerPhone = c.getString(c.getColumnIndex("ownerPhone"));
			pwd.note = c.getString(c.getColumnIndex("note"));
			pwd.date = c.getString(c.getColumnIndex("date"));
			pwd.time = c.getString(c.getColumnIndex("time"));
			pwd.province = c.getString(c.getColumnIndex("province"));
			pwd.city = c.getString(c.getColumnIndex("city"));
			pwd.district = c.getString(c.getColumnIndex("district"));
			pwd.street = c.getString(c.getColumnIndex("street"));
			pwd.industry = c.getString(c.getColumnIndex("industry"));

			pwd.sparebootPwd = c.getString(c.getColumnIndex("sparebootPwd"));// 备用密码
			pwds.add(pwd);
		}
		c.close();
		return pwds;
	}

	/**
	 * query all warns, return cursor
	 * 
	 * @return Cursor
	 */
	public Cursor queryTheCursor() {
		Cursor c = db.rawQuery("SELECT * FROM " + TABLE_PWD
				+ " order by _id desc ", null);
		return c;
	}

	/**
	 * query all warns, return list
	 * 
	 * @param date_end
	 * 
	 * @return List<Person>
	 */
	public List<PwdEntity> queryByDate(Map param) {
		ArrayList<PwdEntity> pwds = new ArrayList<PwdEntity>();
		Cursor c = fetchFromDate(param);
		while (c.moveToNext()) {
			PwdEntity pwd = new PwdEntity();
			pwd._id = c.getInt(c.getColumnIndex("_id"));
			pwd.identityCode = c.getString(c.getColumnIndex("identityCode"));
			pwd.verifyStatus = c.getString(c.getColumnIndex("verifyStatus"));
			pwd.refuseReason = c.getString(c.getColumnIndex("refuseReason"));
			pwd.stripeCode = c.getString(c.getColumnIndex("stripeCode"));
			pwd.openPassword = c.getString(c.getColumnIndex("openPassword"));
			pwd.projectName = c.getString(c.getColumnIndex("projectName"));
			pwd.ownerName = c.getString(c.getColumnIndex("ownerName"));
			pwd.ownerPhone = c.getString(c.getColumnIndex("ownerPhone"));
			pwd.note = c.getString(c.getColumnIndex("note"));
			pwd.date = c.getString(c.getColumnIndex("date"));
			pwd.time = c.getString(c.getColumnIndex("time"));
			pwd.province = c.getString(c.getColumnIndex("province"));
			pwd.city = c.getString(c.getColumnIndex("city"));
			pwd.district = c.getString(c.getColumnIndex("district"));
			pwd.street = c.getString(c.getColumnIndex("street"));
			pwd.industry = c.getString(c.getColumnIndex("industry"));

			pwd.sparebootPwd = c.getString(c.getColumnIndex("sparebootPwd"));// 备用密码
			pwds.add(pwd);
		}
		c.close();
		return pwds;
	}

	// 查询指定fund数据data
	public Cursor fetchFromDate(Map param) throws SQLException {
		StringBuffer hql = new StringBuffer("SELECT * FROM " + TABLE_PWD
				+ " WHERE 1=1 ");
		// 动态拼接条件
		if (param.containsKey("stripeCode")
				&& !param.get("stripeCode").equals("")) {
			hql.append(" and stripeCode like ?");
		}
		if (param.containsKey("date_start")
				&& !param.get("date_start").equals("")) {
			hql.append(" and date>=?");
		}
		if (param.containsKey("date_end") && !param.get("date_end").equals("")) {
			hql.append(" and date<=?");
		}
		hql.append(" order by _id desc");

		// 动态给条件赋值
		ArrayList<String> condition = new ArrayList<String>();
		if (param.containsKey("stripeCode")
				&& !param.get("stripeCode").equals("")) {
			condition.add("%" + param.get("stripeCode").toString() + "%");
		}
		if (param.containsKey("date_start")
				&& !param.get("date_start").equals("")) {
			condition.add(param.get("date_start").toString());
		}
		if (param.containsKey("date_end") && !param.get("date_end").equals("")) {
			condition.add(param.get("date_end").toString() + " 23:59:59");
		}
		String[] array = (String[]) condition.toArray(new String[condition
				.size()]);// 需要转换为数组
		// 查找
		Cursor cursor = db.rawQuery(hql.toString(), array);
		return cursor;
	}

	/**
	 * close database
	 */
	public void closeDB() {
		db.close();
	}
}
